﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using static Dapper.SqlMapper;

namespace Maltose.Data
{
    /// <summary>
    /// Dapper扩展类
    /// </summary>
    public class DapperService : DataBaseService
    {

        /// <summary>
        /// Dapper扩展类
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        public DapperService(string connectionString) : base(connectionString) { }

        /// <summary>
        /// Dapper扩展类
        /// </summary>
        /// <param name="dataBase">数据库版本选择</param>
        public DapperService(DataBase dataBase) : base(dataBase) { }


        #region 查询数据集合

        /// <summary>
        /// 获取数据集合
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">根据枚举解析的命令字符串</param>
        /// <param name="param">参数</param>

        public IEnumerable<T> List<T>(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.Query<T>(commText, param, null, true, null, commandType);
            }
        }

        /// <summary>
        ///  获取数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> List<T>(string strSql, object param = null)
        {
            return List<T>(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandType"></param>
        /// <param name="commText"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<IEnumerable<T>> ListAsync<T>(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.QueryAsync<T>(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 获取数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<IEnumerable<T>> ListAsync<T>(string strSql = null, object param = null)
        {
            return ListAsync<T>(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 执行储存过程获取数据集合
        /// </summary>
        /// <typeparam name="T">实体类类型</typeparam>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public IEnumerable<T> ExecProcList<T>(string procName, DynamicParameters param = null)
        {
            return List<T>(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 执行储存过程获取数据集合
        /// </summary>
        /// <typeparam name="T">实体类类型</typeparam>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> ExecProcListAsync<T>(string procName, DynamicParameters param = null)
        {
            return await ListAsync<T>(CommandType.StoredProcedure, procName, null);
        }

        #endregion

        #region 查询单列数据

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="commandType">枚举</param>
        /// <param name="commandText">指定的枚举解析字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T Single<T>(CommandType commandType, string commandText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.Query<T>(commandText, param, null, true, null, commandType).FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="commandType">枚举</param>
        /// <param name="commandText">指定的枚举解析字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<T> SingleAsync<T>(CommandType commandType, string commandText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.QueryFirstOrDefaultAsync<T>(commandText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T Single<T>(string strSql, object param = null)
        {
            return Single<T>(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<T> SingleAsync<T>(string strSql, object param = null)
        {
            return SingleAsync<T>(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T ExecProcSingle<T>(string procName, DynamicParameters param = null)
        {
            return Single<T>(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<T> ExecProcSingleAsync<T>(string procName, DynamicParameters param = null)
        {
            return SingleAsync<T>(CommandType.StoredProcedure, procName, param);
        }

        #endregion

        #region 添加

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Insert(string strSql, object param = null)
        {
            return NoQuery(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<int> InsertAsync(string strSql, object param = null)
        {
            return NoQueryAsync(CommandType.Text, strSql, param);
        }

        #endregion

        #region 修改

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Update(string strSql, object param = null)
        {
            return NoQuery(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<int> UpdateAsync(string strSql, object param = null)
        {
            return NoQueryAsync(CommandType.Text, strSql, param);
        }

        #endregion

        #region 删除
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Delete(string strSql, object param = null)
        {
            return NoQuery(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<int> DeleteAsync(string strSql, object param = null)
        {
            return NoQueryAsync(CommandType.Text, strSql, param);
        }

        #endregion

        #region 执行非查询

        /// <summary>
        /// 执行语句返回影响行数
        /// </summary>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">枚举指定解析类型字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int NoQuery(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.Execute(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 执行语句返回影响行数
        /// </summary>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">枚举指定解析类型字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<int> NoQueryAsync(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.ExecuteAsync(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 执行SQL语句返回影响行数
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>

        public int NoQuery(string strSql, object param = null)
        {
            return NoQuery(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 执行SQL语句返回影响行数
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>

        public Task<int> NoQueryAsync(string strSql, object param = null)
        {
            return NoQueryAsync(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 执行储存过程 返回影响行数
        /// </summary>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int ExecProcNoQuery(string procName, DynamicParameters param = null)
        {
            return NoQuery(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 执行储存过程 返回影响行数
        /// </summary>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<int> ExecProcNoQueryAsync(string procName, DynamicParameters param = null)
        {
            return NoQueryAsync(CommandType.StoredProcedure, procName, param);
        }

        #endregion

        #region 查询单行单列

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">枚举指定解析类型字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public object Scalar(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.ExecuteScalar(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">枚举指定解析类型字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T Scalar<T>(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return (T)conn.ExecuteScalar(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">枚举指定解析类型字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<object> ScalarAsync(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.ExecuteScalarAsync(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="commandType">枚举</param>
        /// <param name="commText">枚举指定解析类型字符串</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<T> ScalarAsync<T>(CommandType commandType, string commText, object param = null)
        {
            using (var conn = IConnection)
            {
                return conn.ExecuteScalarAsync<T>(commText, param, null, null, commandType);
            }
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public object Scalar(string strSql, object param = null)
        {
            return Scalar(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T Scalar<T>(string strSql, object param = null)
        {
            return Scalar<T>(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<object> ScalarAsync(string strSql, object param = null)
        {
            return ScalarAsync(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<T> ScalarAsync<T>(string strSql, object param = null)
        {
            return ScalarAsync<T>(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public object ExecProcScalar(string procName, DynamicParameters param = null)
        {
            return Scalar(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T ExecProcScalar<T>(string procName, DynamicParameters param = null)
        {
            return Scalar<T>(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<object> ExecProcScalarAsync(string procName, DynamicParameters param = null)
        {
            return ScalarAsync(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 获取一行一列值
        /// </summary>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public Task<T> ExecProcScalarAsync<T>(string procName, DynamicParameters param = null)
        {
            return ScalarAsync<T>(CommandType.StoredProcedure, procName, param);
        }

        #endregion

        #region 查询单条数据量

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commText"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public long Count(CommandType commandType, string commText, object param = null)
        {
            return Convert.ToInt64(Scalar(commandType, commText, param));
        }

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public long Count(string strSql, object param = null)
        {
            return Count(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commText"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<long> CountAsync(CommandType commandType, string commText, object param = null)
        {
            return Task.Run(() =>
            {
                return Convert.ToInt64(ScalarAsync(CommandType.Text, commText, param));
            });
        }

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<long> CountAsync(string strSql, object param = null)
        {
            return CountAsync(CommandType.Text, strSql, param);
        }

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public long ExecProcCount(string procName, DynamicParameters param = null)
        {
            return Count(CommandType.StoredProcedure, procName, param);
        }

        /// <summary>
        /// 查询数据数量
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Task<long> ExecProcCountAsync(string procName, DynamicParameters param = null)
        {
            return CountAsync(CommandType.StoredProcedure, procName, param);
        }

        #endregion

        #region  执行储存过程 返回单个/多个输出参数

        /// <summary>
        /// 执行储存过程
        /// </summary>
        /// <param name="procName">储存过程名称</param>
        /// <param name="param">参数</param>

        public void ExecProc(string procName, DynamicParameters param = null)
        {
            using (var conn = IConnection)
            {
                conn.Execute(procName, param, null, null, CommandType.StoredProcedure);
            }
        }

        #endregion

        #region 数据库事务提交

        /// <summary>
        /// 批量数据事务提交
        /// </summary>
        /// <param name="strSql">T-SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int ExecTransaction(string strSql, object param = null)
        {
            using (var conn = IConnection)
            {
                var s = 0;
                var tran = conn.BeginTransaction();
                try
                {
                    s = conn.Execute(strSql, param, tran);
                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();
                }
                return s;
            }
        }

        #endregion  

        #region 分页

        /// <summary>
        /// 分页数据返回T类型
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public SqlPageData<T> PageList<T>(SqlPageNormal param) where T : class, new()
        {
            using (var conn = IConnection)
            {
                var sql = SqlPageBuilder.NormalSQL(param);
                var multi = conn.QueryMultiple(sql, param.Where.Parameters, null, null, null);
                return new SqlPageData<T>()
                {
                    Data = multi.Read<T>(),
                    DataCount = multi.Read<long>().Single(),
                    PageIndex = param.PageIndex,
                    PageSize = param.PageSize
                };
            }
        }

        /// <summary>
        /// 分页数据返回T类型
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public IEnumerable<T> PageList<T>(SqlPageNormalNoneCount param) where T : class, new()
        {
            var sql = SqlPageBuilder.NormalSQLNoneCount(param);
            return List<T>(sql, param.Where.Parameters);
        }




        /// <summary>
        /// 分页数据返回T类型
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public SqlPageData<T> PageList<T>(SqlPageCustom param) where T : class, new()
        {
            using (var conn = IConnection)
            {
                var sql = SqlPageBuilder.CustomSQL(param);
                var multi = conn.QueryMultiple(sql, param.Where.Parameters, null, null, null);
                return new SqlPageData<T>()
                {
                    Data = multi.Read<T>(),
                    DataCount = multi.Read<long>().Single(),
                    PageIndex = param.PageIndex,
                    PageSize = param.PageSize
                };
            }
        }

        /// <summary>
        /// 分页数据返回T类型
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public IEnumerable<T> PageList<T>(SqlPageCustomNoneCount param) where T : class, new()
        {
            var sql = SqlPageBuilder.CustomSQLNoneCount(param);
            return List<T>(sql, param.Where.Parameters);
        }


        #endregion

    }
}
